package com.service.task.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.pub.db.DBConnectionManager;
import com.pub.exception.NullEnvironmentVariableException;
import com.pub.util.StringUtils;
import com.service.task.dao.TaskDAO;
import com.service.task.domain.Task;


public class TaskImpl implements TaskDAO
{
	private static Logger logger;    
	
	private String DBTag;
	
	private DBConnectionManager dbcm;
	
	static
	{
		logger = Logger.getLogger (TaskImpl.class.getName());
	}
	
	public TaskImpl(String DBTag)
	{
		this.DBTag 	= DBTag;
		try {
			dbcm 	= DBConnectionManager.getInstance(TaskImpl.class.getName());
		} catch (NullEnvironmentVariableException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int insert(Task task) 
	{
		int id = 0;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sbSql.append("INSERT INTO task SET createdTime = UTC_TIMESTAMP(), modifiedTime = UTC_TIMESTAMP(), taskTypeid = ");
			sbSql.append(task.getTaskTypeid());
			
			sbSql.append(", userid = ");
			sbSql.append(task.getUserid());
			
			sbSql.append(", period = ");
			sbSql.append(task.getPeriod());
			
			sbSql.append(", countryid = '");
			sbSql.append(task.getCountryid());
			
			sbSql.append(", delay = '");
			sbSql.append(task.getDelay());
			
			sbSql.append("', time = '");
			sbSql.append(task.getTime());
			
			sbSql.append("', status = '");
			sbSql.append(task.getStatus());
			
			sbSql.append("', name = '");
			sbSql.append(StringUtils.addcslashes(task.getName()));
			
			sbSql.append("', summary='");
			sbSql.append(StringUtils.addcslashes(task.getSummary()));
			
			sbSql.append("', xml='");
			sbSql.append(StringUtils.addcslashes(task.getXml()));
			
			sbSql.append("', xmlPath='");
			sbSql.append(StringUtils.addcslashes(task.getXmlPath()));
			
			sbSql.append("'");
			
			sm.executeUpdate(sbSql.toString(), Statement.RETURN_GENERATED_KEYS);
			rs = sm.getGeneratedKeys();
			if ( rs.next() ) {
				id = rs.getInt(1);
			}
		}catch(Exception e){
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(id == 0 && conn != null){
				try {
					conn.rollback();
				} catch (SQLException e) {
				}
			}
			
			try{
				if(rs != null)
					rs.close();
			}catch(SQLException e){}
			
			try{
				if(sm != null)
					sm.close();
			}catch(SQLException e){}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return id;
	}

	
	public int update(Task task) 
	{
		int updateCount = 0;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			
			sbSql.append("INSERT INTO task SET modifiedTime = UTC_TIMESTAMP(), taskTypeid = ");
			sbSql.append(task.getTaskTypeid());
			
			sbSql.append(", userid = ");
			sbSql.append(task.getUserid());
			
			sbSql.append(", period = ");
			sbSql.append(task.getPeriod());
			
			sbSql.append(", countryid = '");
			sbSql.append(task.getCountryid());
			
			sbSql.append(", delay = '");
			sbSql.append(task.getDelay());
			
			sbSql.append("', time = '");
			sbSql.append(task.getTime());
			
			sbSql.append("', status = '");
			sbSql.append(task.getStatus());
			
			sbSql.append("', name = '");
			sbSql.append(StringUtils.addcslashes(task.getName()));
			
			sbSql.append("', summary='");
			sbSql.append(StringUtils.addcslashes(task.getSummary()));
			
			sbSql.append("', xml='");
			sbSql.append(StringUtils.addcslashes(task.getXml()));
			
			sbSql.append("', xmlPath='");
			sbSql.append(StringUtils.addcslashes(task.getXmlPath()));
			
			sbSql.append("' ON DUPLICATE KEY UPDATE countryid=VALUES(countryid), userid=VALUES(userid), xmlPath=VALUES(xmlPath), xml=VALUES(xml), time=VALUES(time), delay=VALUES(delay), modifiedTime=VALUES(modifiedTime), period=VALUES(period), name=VALUES(name), summary=VALUES(summary)");
		
			updateCount = sm.executeUpdate(sbSql.toString());
		}catch(Exception e){
			logger.error("table object insert operation error.");
			e.printStackTrace();
		}finally{
			if(updateCount == 0 && conn != null){
				try {
					conn.rollback();
				} catch (SQLException e) {
					logger.error("object rollback to insert operation error.");
					e.printStackTrace();
				}
			}
			
			try{
				if(rs != null)
					rs.close();
			}catch(SQLException e){}
			
			try{
				if(sm != null)
					sm.close();
			}catch(SQLException e){}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return updateCount;
	}
	
	@Override
	public Vector<Task> getList(int countryid, int taskTypeid)
	{
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;

		Vector<Task> vtReturn = new Vector<Task>(82);
		
		StringBuilder sbSql = new StringBuilder(200);
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
	          
			sbSql.append("SELECT id, userid, taskTypeid, period, delay, status, name, summary, createdTime, modifiedTime, xmlPath, xml, time FROM task WHERE status ='Y' AND countryid=");
			sbSql.append(countryid);
			
			sbSql.append(" AND taskTypeid = ");
			sbSql.append(taskTypeid);
			
			Task task;
			rs 	= sm.executeQuery(sbSql.toString());
			
			while(rs.next()){
				task = new Task();
				
				task.setCountryid(countryid);
				
				task.setID(rs.getInt(1));
				task.setUserid(rs.getInt(2));
				task.setTaskTypeid(rs.getInt(3));
				
				task.setPeriod(rs.getInt(4));
				task.setDelay(rs.getInt(5));
				task.setStatus(rs.getString(6));
				
				task.setName(rs.getString(7));
				task.setSummary(rs.getString(8));
				
				task.setCreatedTime(rs.getString(9));
				task.setModifiedTime(rs.getString(10));
				
				task.setXmlPath(rs.getString(11));
				task.setXml(rs.getString(12));
				task.setTime(rs.getString(13));
				
				vtReturn.add(task);
			}
		} catch (Exception e) {
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if(sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
				}
			}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return vtReturn;
	}

	public void close() 
	{
		dbcm.release(TaskImpl.class.getName());
	}


}
